﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Nhibernate;
using NHibernate.Criterion;
using NHibernate;
using System.Data.SqlClient;
using System.Data;

namespace Bussiness.Service
{
  public  class Artical_Service
    {
      //public List<CatelogriesArticle> getAll_CATELOGIES_Article()
      //  {
      //      return new HibernateDAO<CatelogriesArticle, String>().getBySQL("select * from Catelories_Product");
      //  }

      public List<Article> getAll_Article()
        {
            return new HibernateDAO<Article, String>().getBySQL("select * from Article");
        }

      public List<Article> getArticleByIsBanner()
      {
          return new HibernateDAO<Article, String>().getBySQL("select * from Article where is_Banner = 'True'");
      }

      public List<Article> search_Article(Article product)
        {
            HibernateDAO<Article, String> dao = new HibernateDAO<Article, String>();

            if (!Formater.isNull(product.Title))
                dao.addSearchCriteria(Expression.Like("Code", product.Title, MatchMode.Anywhere).IgnoreCase());

            if (!Formater.isNull(product.IdCategories.ToString()))
            dao.addSearchCriteria(Expression.Eq("IdCategories", product.IdCategories));

            DateTime newDate = new DateTime(1, 1, 1);
            if (product.Date != newDate && product.DateEnd != newDate)
            dao.addSearchCriteria(Expression.Between("Date",product.Date,product.DateEnd));

            return dao.getByCriterion();
        }

      public void delete_Article(int id)
        {
            new HibernateDAO<Article, string>().ExecuteSql("delete Article where id = '" + id + "'");
        }

      public void save_Article(Article product)
        {
            new HibernateDAO<Article, String>().SaveOrUpdate(product);
        }

      public Article getArticleByID(int id)
        {
            using (ISession session = NHibernateSessionManager.GetCurrentSession())
            {
                Article product = session
                    .CreateCriteria(typeof(Article))
                    .Add(Restrictions.Eq("ID", id))
                    .UniqueResult<Article>();
                return product;
            }
        }

      public List<Article> get_Article_By_idCatelogy_Paging(int id, int pageIndex,int pageSize)
      {
          ISession session = null;
          try
          {
              session = NHibernateSessionManager.GetCurrentSession();
              var list = (List<Article>)session.GetNamedQuery("Get_ArticlePaging")
                          .SetParameter("CatelogyID", id)
                          .SetParameter("pageIndex", pageIndex)
                          .SetParameter("pageSize", pageSize)
                          .List<Article>();
             
              return list;
          }
          catch (Exception e)
          {
              return null;
          }


      }

      public List<int> get_List_Page_Article(int idCatelogy, int pageSize)
      {
          int o = 0;
          int count = 0;
          List<int> list = new List<int>();
          try
          {

              string connect = System.Configuration.ConfigurationManager.ConnectionStrings["VATConnectionString"].ToString();
              SqlConnection scn = new SqlConnection(connect);
              scn.Open();
              SqlCommand spcmd = new SqlCommand("Select count(ID) from Article where id_categories ='" + idCatelogy + "'", scn);
              spcmd.CommandType = CommandType.Text;
              SqlDataAdapter da = new SqlDataAdapter(spcmd);
              o = (int)spcmd.ExecuteScalar();
              if ((o % pageSize) == 0)
              {
                  count = (o / pageSize);
                  for (int i = 1; i <= count; i++)
                  {
                      list.Add(i);
                  }

              }
              else
              {
                  count = (o / pageSize) + 1;

                  for (int i = 1; i <= count; i++)
                  {
                      list.Add(i);
                  }

              }
              return list;
              scn.Close();
          }


          catch (SqlException x)
          {
              throw;
              return null;
          }


      }



    }
}
